package com.ly.gen.service;

import cn.hutool.json.JSONObject;
import com.ly.gen.model.ConnInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * conns service
 *
 * @author yuan.lei
 * @date 2023/2/7
 **/
@Service
public class ConnsService {

    private static final String QUERY_SQL = "select * from conns";
    private static final String INSERT_SQL = "INSERT INTO conns(conn_name, conn_addr, user_name, password, db_type) VALUES (:connName, :connAddr, :userName, :password, :dbType);";
    private static final String UPDATE_SQL = "update conns set conn_name = :connName,conn_addr = :connAddr,user_name = :userName, password = :password, db_type = :dbType where id = :id";

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    /**
     * query conns table data
     *
     * @author yuan.lei
     * @date 2023/2/7
     **/
    public List<ConnInfo> queryList(Map<String, Object> params) {
        return jdbcTemplate.query(QUERY_SQL, params, new ConnRowMapper());
    }

    /**
     * insert conn data
     *
     * @author yuan.lei
     * @date 2023/2/7
     **/
    public void saveConn(ConnInfo connInfo) {
        jdbcTemplate.update(INSERT_SQL, new JSONObject(connInfo));
    }

    /**
     * update conn info
     *
     * @author leiyuan
     * @date 2023/2/22 0022
     **/
    public void updateConn(ConnInfo connInfo) {
        JSONObject data = new JSONObject(connInfo);
        jdbcTemplate.update(UPDATE_SQL, data);
    }

    /**
     * 根据id获取连接信息
     *
     * @author leiyuan
     * @date 2023/2/19 0019
     **/
    public Map<String, Object> getConn(Integer connId) {
        Map<String, Object> params = new HashMap<>();
        params.put("id", connId);
        return jdbcTemplate.queryForMap(QUERY_SQL + " where id = :id", params);
    }

    /**
     * 根据id获取数据库类型
     *
     * @author leiyuan
     * @date 2023/2/19 0019
     **/
    public String getDbType(Integer connId) {
        Map<String, Object> data = getConn(connId);
        if (data != null) {
            return data.get("db_type").toString();
        }
        return "";

    }

    public void delConn(Integer id) {
        Map<String, Object> params = new HashMap<>();
        params.put("id", id);
        jdbcTemplate.update("delete from conns where id = :id", params);
    }

    class ConnRowMapper implements RowMapper<ConnInfo> {
        @Override
        public ConnInfo mapRow(ResultSet rs, int rowNum) throws SQLException {
            ConnInfo info = new ConnInfo();
            info.setId(rs.getInt(1));
            info.setConnName(rs.getString(2));
            info.setConnAddr(rs.getString(3));
            info.setUserName(rs.getString(4));
            info.setPassword(rs.getString(5));
            info.setDbType(rs.getString(6));
            return info;
        }
    }
}
